package org.lc.kcjxzj.service.impl;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.compress.archivers.zip.ZipFile;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.lc.kcjxzj.dto.ImportResultDTO;
import org.lc.kcjxzj.model.Kcjxzj;
import org.lc.kcjxzj.repository.KcjxzjRepository;
import org.lc.kcjxzj.service.ImportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
@Transactional
public class ImportServiceImpl implements ImportService {

    // 添加内部类用于存储单元格配置
    private static class CellConfig {
        int sheetIndex;
        int rowIndex;
        int columnIndex;

        public CellConfig(int sheetIndex, int rowIndex, int columnIndex) {
            this.sheetIndex = sheetIndex;
            this.rowIndex = rowIndex;
            this.columnIndex = columnIndex;
        }
    }

    @Autowired
    private KcjxzjRepository kcjxzjRepository;

    @Override
    public ImportResultDTO importFromArchive(MultipartFile file) {
        ImportResultDTO result = new ImportResultDTO();
        Path tempDir = null;
        
        try {
            tempDir = Files.createTempDirectory("kcjxzj_import_");
            Path zipPath = tempDir.resolve("upload.zip");
            file.transferTo(zipPath);  // 将上传文件保存为临时文件
            
            // 使用支持自动编码检测的 ZipFile
            try (ZipFile zipFile = new ZipFile(zipPath.toFile(), "GBK")) {
                zipFile.getEntries().asIterator().forEachRemaining(entry -> {
                    if (!entry.getName().toLowerCase().endsWith(".xlsx")) return;
                    
                    result.incrementTotal();
                    try (InputStream entryStream = zipFile.getInputStream(entry)) {
                        byte[] content = IOUtils.toByteArray(entryStream);
                        ImportResultDTO excelResult = importFromExcel(content, entry.getName());
                        if (excelResult.getFailureCount() > 0) {
                            result.getErrors().addAll(excelResult.getErrors());
                            result.addError("文件 " + entry.getName() + " 导入失败");
                        } else {
                            String fileName = new String(entry.getName().getBytes("GBK"), "UTF-8");
                            result.addSuccessFile(fileName);
                        }
                    } catch (Exception e) {
                        result.addError("处理文件 " + entry.getName() + " 时发生错误: " + e.getMessage());
                        log.error("处理Excel文件失败: {}", entry.getName(), e);
                    }
                });
            }

        } catch (Exception e) {
            result.addError("处理压缩文件时发生错误: " + e.getMessage());
            log.error("导入失败", e);
        } finally {
            // 清理临时文件
            if (tempDir != null) {
                try {
                    FileUtils.deleteDirectory(tempDir.toFile());
                } catch (IOException e) {
                    log.error("清理临时文件失败", e);
                }
            }
        }
        
        return result;
    }

    @Override
    public ImportResultDTO importFromExcel(byte[] excelFile, String fileName) {
        ImportResultDTO result = new ImportResultDTO();
        result.incrementTotal();
        
        try (Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(excelFile))) {
            Kcjxzj kcjxzj = new Kcjxzj();

            // 定义字段配置（sheet索引，行号，列号）
            Map<String, CellConfig> configs = new LinkedHashMap<>();
            configs.put("学期", new CellConfig(0, 8, 0));  // Sheet 0 (A9)
            configs.put("课程名称", new CellConfig(0, 24, 3)); // Sheet 0 (D25)
            configs.put("任课教师", new CellConfig(0, 26, 3)); // Sheet 0 (D27)
            configs.put("教学班次", new CellConfig(1, 4, 4));  // Sheet 1 (E5)
            configs.put("主要教学方法", new CellConfig(1, 7, 4)); // Sheet 1 (E8)
            // 删除固定位置的试卷分析和成绩分析
            configs.put("课程教学总结", new CellConfig(4, 1, 2));  // Sheet 4 (C2)
            configs.put("意见建议", new CellConfig(5, 1, 2));   // Sheet 5 (C2)

            // 遍历配置读取单元格
            for (Map.Entry<String, CellConfig> entry : configs.entrySet()) {
                String field = entry.getKey();
                CellConfig cfg = entry.getValue();
                
                try {
                    Sheet sheet = workbook.getSheetAt(cfg.sheetIndex);
                    Row row = sheet.getRow(cfg.rowIndex);
                    if (row == null) {
                        throw new RuntimeException(field + " 行不存在");
                    }
                    
                    Cell cell = row.getCell(cfg.columnIndex);
                    String value = getCellValueAsString(cell);
                    
                    switch (field) {
                        case "学期": kcjxzj.setXueqi(value); break;
                        case "课程名称": kcjxzj.setCourse(value); break;
                        case "任课教师": kcjxzj.setTeacher(value); break;
                        case "教学班次": kcjxzj.setTclass(value); break;
                        case "主要教学方法": kcjxzj.setJxff(value); break;
                        case "课程教学总结": kcjxzj.setZj(value); break;
                        case "意见建议": kcjxzj.setYjjy(value); break;
                    }
                } catch (Exception e) {
                    throw new RuntimeException(field + " 字段读取失败: " + e.getMessage());
                }
            }

            // 在Sheet 1中查找动态标签字段
            try {
                Sheet sheet = workbook.getSheetAt(1);
                
                // 处理"形成性考核情况分析"
                findAndSetCellValueByLabel(sheet, "形成性考核情况分析", value -> kcjxzj.setXcxfx(value));
                
                // 处理"考核试卷（题目）分析"
                findAndSetCellValueByLabel(sheet, "考核试卷（题目）分析", value -> kcjxzj.setSjfx(value));
                
                // 处理"考核成绩分析"
                findAndSetCellValueByLabel(sheet, "考核成绩分析", value -> kcjxzj.setCjfx(value));
                
            } catch (Exception e) {
                throw new RuntimeException("动态字段读取失败: " + e.getMessage());
            }
            
            // 验证必填字段
            List<String> missingFields = validateKcjxzj(kcjxzj);
            if (!missingFields.isEmpty()) {
                result.addError(fileName + " 缺少必填字段: " + String.join(", ", missingFields));
                return result;
            }
            
            // 保存到数据库
            kcjxzjRepository.save(kcjxzj);
            result.addSuccessFile(fileName);
            
        } catch (Exception e) {
            result.addError(fileName + " 处理失败: " + e.getMessage());
            log.error("处理Excel文件失败", e);
        }
        
        return result;
    }
    
    /**
     * 通过内容查找单元格
     * @param sheet Excel表格
     * @param content 要查找的内容
     * @return 找到的单元格，如果未找到则返回null
     */
    private Cell findCellByContent(Sheet sheet, String content) {
        // 规范化搜索内容：去除空格、换行符等
        String normalizedContent = normalizeString(content);
        
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        // 规范化单元格内容并比较
                        String cellValue = getCellValueAsString(cell);
                        if (normalizedContent.equals(normalizeString(cellValue))) {
                            return cell;
                        }
                    }
                }
            }
        }
        return null;
    }

    /**
     * 通过标签查找单元格并设置对应的值
     * @param sheet Excel表格
     * @param labelText 标签文本
     * @param valueConsumer 值处理函数
     */
    private void findAndSetCellValueByLabel(Sheet sheet, String labelText, java.util.function.Consumer<String> valueConsumer) {
        Cell labelCell = findCellByContent(sheet, labelText);
        if (labelCell != null) {
            Row row = labelCell.getRow();
            Cell valueCell = row.getCell(labelCell.getColumnIndex() + 1);
            String value = getCellValueAsString(valueCell);
            valueConsumer.accept(value);
        } else {
            log.warn("未找到'{}' 单元格标识", labelText);
        }
    }

    /**
     * 规范化字符串，移除所有空白字符（空格、制表符、换行符等）
     * 支持中文字符的标准化处理
     * @param str 输入字符串
     * @return 规范化后的字符串
     */
    private String normalizeString(String str) {
        if (str == null) return "";
        
        // 1. 使用Unicode标准化确保字符表示的一致性（特别是对中文字符）
        // 2. 移除所有空白字符（空格、制表符、换行符等）
        // 3. 转换为小写（仅影响拉丁字符，不影响中文）
        return java.text.Normalizer.normalize(str, java.text.Normalizer.Form.NFC)
               .replaceAll("\\s+", "")
               .toLowerCase();
    }
    
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue().toString();
                }
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return "";
        }
    }
    
    private List<String> validateKcjxzj(Kcjxzj kcjxzj) {
        List<String> missingFields = new ArrayList<>();
        if (kcjxzj.getXueqi() == null || kcjxzj.getXueqi().isEmpty()) {
            missingFields.add("学期");
        }
        if (kcjxzj.getCourse() == null || kcjxzj.getCourse().isEmpty()) {
            missingFields.add("课程名称");
        }
        if (kcjxzj.getTeacher() == null || kcjxzj.getTeacher().isEmpty()) {
            missingFields.add("任课教师");
        }
        return missingFields;
    }
    @Override
    public ImportResultDTO importFromExcelSheet(MultipartFile file, String semester) {
        ImportResultDTO result = new ImportResultDTO();
        try (Workbook workbook = new XSSFWorkbook(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rows = sheet.iterator();
            if (rows.hasNext()) rows.next(); // 跳过标题行
            while (rows.hasNext()) {
                Row row = rows.next();
                String teacher = getCellValueAsString(row.getCell(0));
                String course = getCellValueAsString(row.getCell(1));
                String tclass = getCellValueAsString(row.getCell(2));
                String jxff = getCellValueAsString(row.getCell(3));
                String xcxfx = getCellValueAsString(row.getCell(4));
                String sjfx = getCellValueAsString(row.getCell(5));
                String cjfx = getCellValueAsString(row.getCell(6));
                String zj = getCellValueAsString(row.getCell(7));
                String yjjy = getCellValueAsString(row.getCell(8));

                Kcjxzj record = new Kcjxzj();
                record.setXueqi(semester);
                record.setTeacher(teacher);
                record.setCourse(course);
                record.setTclass(tclass);
                record.setJxff(jxff);
                record.setXcxfx(xcxfx);
                record.setSjfx(sjfx);
                record.setCjfx(cjfx);
                record.setZj(zj);
                record.setYjjy(yjjy);

                if (!validateKcjxzj(record).isEmpty()) {
                    result.addError("Excel行缺少必填字段");
                } else {
                    kcjxzjRepository.save(record);
                    result.addSuccessFile("Excel row imported");
                }
            }
        } catch (Exception e) {
            result.addError("Excel汇总导入失败: " + e.getMessage());
        }
        return result;
    }
}